<?php
    class Query extends CI_Model{
        function __construct(){
            // Call the Model constructor
            parent::__construct();
        }

        public function getList(){        
            $select = "SELECT * FROM queries";
            $query = $this->db->query($select);
            $res = $query->result();
            foreach($res as $item){
                $item->query_sql = $this->generalSQL($item->query_id);
            }
            return $res;
        }
        
        public function generalSQL($queyId){
            $selects = $this->getListSelect($queyId);
            $tables = $this->getListTable($queyId);
            $tableJoins = $this->getListTableJoins($queyId);
            $wheres = $this->getListWheres($queyId);
            $orders = $this->getListOrders($queyId);
            
            $query = "SELECT ";
            
            //general select
            $strSelect = "";
            foreach($selects as $select){
                $alias = empty($select->alias) ? "" : " as ".$select->alias;
                $strSelect .= ' '.$select->table_name.'.'.$select->field_name.$alias.',';
            }
            $strSelect = trim($strSelect, ',');
            
            //general from table
            $strTable = " FROM ";
            foreach($tables as $table){
                $alias = empty($table->query_table_alias) ? "" : " as ".$table->query_table_alias;
                $strTable .= $table->query_table_name.$alias.',';
            }
            $strTable = trim($strTable, ',');
            
            //general from joins
            $strTableJoin = " ";
            foreach($tableJoins as $tableJoin){
                $strTableJoin .= ' '.strtoupper($tableJoin->query_table_join_type).
                    ' JOIN '.$tableJoin->query_table_join_name.' ON '.
                        $tableJoin->query_table_join_name.'.'.
                        $tableJoin->query_table_join_field.' = '.
                        $tableJoin->query_table_join_name2.'.'.
                        $tableJoin->query_table_join_field2;
            }
            
            //general query
            $strWhere = "";
            foreach($wheres as $where){
                $conditionType = empty($where->condition_type) ? "" : strtoupper($where->condition_type);
                $strWhere .= " ".$conditionType." ".$where->condition_name." ".
                    $where->query_where_operation." ".
                    $where->condition_value;
            }
            $strWhere = empty($strWhere) ? "" : " WHERE ".$strWhere;
            
            //general order by
            $strOrder = " ORDER BY ";
            foreach($orders as $order){
                $strOrder .= " ".$order->table_name.".".$order->table_field." ".
                    strtoupper($order->order_type).",";
            }
            $strOrder = trim($strOrder, ',');
            
            $query .= $strSelect.$strTable.$strTableJoin.$strWhere.$strOrder;
            return $query;
        }
        
        public function getListSelect($queryId){
            //$query = $this->db->get('query_selects');
            $select = "SELECT * FROM query_selects WHERE query_id = ".$queryId;
            $query = $this->db->query($select);
            return $query->result(); 
        }
        
        public function getListTable($queryId){
            //$query = $this->db->get('query_tables');
            $select = "SELECT * FROM query_tables WHERE query_id = ".$queryId;
            $query = $this->db->query($select);
            return $query->result(); 
        }
        
        public function getListTableJoins($queryId){
            //$query = $this->db->get('query_table_joins');
            $select = "SELECT * FROM query_table_joins WHERE query_id = ".$queryId;
            $query = $this->db->query($select);
            return $query->result(); 
        }
        
        public function getListWheres($queryId){
            //$query = $this->db->get('query_wheres');
            $select = "SELECT * FROM query_wheres WHERE query_id = ".$queryId;
            $query = $this->db->query($select);
            return $query->result();     
        }
        
        public function getListOrders($queryId){
            $select = "SELECT * FROM query_orders WHERE query_id = ".$queryId;
            $query = $this->db->query($select);
            return $query->result();     
        }
    }  
?>